/*
 Navicat Premium Data Transfer

 Source Server         : 本机SQL Server
 Source Server Type    : SQL Server
 Source Server Version : 13001742
 Source Host           : localhost:1433
 Source Catalog        : Hotel
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 13001742
 File Encoding         : 65001

 Date: 04/01/2022 20:06:14
*/


-- ----------------------------
-- Table structure for t_vip
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_vip]') AND type IN ('U'))
	DROP TABLE [dbo].[t_vip]
GO

CREATE TABLE [dbo].[t_vip] (
  [id] int  IDENTITY(1,1) NOT NULL,
  [name] varchar(255) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [cardid] varchar(30) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [phone] varchar(20) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [level] varchar(10) COLLATE Chinese_PRC_CI_AS  NULL,
  [discount] int  NULL,
  [points] int  NULL
)
GO

ALTER TABLE [dbo].[t_vip] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of t_vip
-- ----------------------------
SET IDENTITY_INSERT [dbo].[t_vip] ON
GO

INSERT INTO [dbo].[t_vip] ([id], [name], [cardid], [phone], [level], [discount], [points]) VALUES (N'1', N'李四', N'370000200000001234', N'10000000000', N'黄金会员', N'90', N'1170')
GO

INSERT INTO [dbo].[t_vip] ([id], [name], [cardid], [phone], [level], [discount], [points]) VALUES (N'2', N'老八', N'221237298810011221', N'19400001111', N'青铜会员', N'97', N'344')
GO

INSERT INTO [dbo].[t_vip] ([id], [name], [cardid], [phone], [level], [discount], [points]) VALUES (N'3', N'葱香', N'123567890234567', N'1212312311', N'普通会员', N'99', N'97')
GO

INSERT INTO [dbo].[t_vip] ([id], [name], [cardid], [phone], [level], [discount], [points]) VALUES (N'4', N'v1', N'172612535182491', N'7125182481', N'普通会员', N'99', N'67')
GO

INSERT INTO [dbo].[t_vip] ([id], [name], [cardid], [phone], [level], [discount], [points]) VALUES (N'5', N'李11四', N'110000200000001234', N'1011111000', N'青铜会员', N'97', N'144')
GO

INSERT INTO [dbo].[t_vip] ([id], [name], [cardid], [phone], [level], [discount], [points]) VALUES (N'6', N'vip1', N'11111111111111111111111', N'11111111111111111', N'普通会员', N'99', N'0')
GO

SET IDENTITY_INSERT [dbo].[t_vip] OFF
GO


-- ----------------------------
-- Auto increment value for t_vip
-- ----------------------------
DBCC CHECKIDENT ('[dbo].[t_vip]', RESEED, 6)
GO


-- ----------------------------
-- Triggers structure for table t_vip
-- ----------------------------
CREATE TRIGGER [dbo].[vip_level_add]
ON [dbo].[t_vip]
WITH EXECUTE AS CALLER
FOR UPDATE
AS
begin
	declare @id int, @p1 int, @p2 int, @level varchar(10), @discount int
	select @p1=points from deleted 
	select @id=id,@p2=points from inserted
	if @p1 != @p2 and @p1 is not null and @p2 is not null
	begin
		if @p2 < 101
		begin
			set @level = '普通会员'
			set @discount = 99
		end
		if @p2 > 100
		begin
			set @level = '青铜会员'
			set @discount = 97
		end
		if @p2 > 500
		begin
			set @level = '白银会员'
			set @discount = 95
		end
		if @p2 > 1000
		begin
			set @level = '黄金会员'
			set @discount = 90
		end
		if @p2 > 2000
		begin
			set @level = '铂金会员'
			set @discount = 85
		end
		if @p2 > 4000 
		begin
			set @level = '钻石会员'
			set @discount = 80
		end
		update t_vip set level=@level, discount=@discount where id=@id
	end
end
GO


-- ----------------------------
-- Uniques structure for table t_vip
-- ----------------------------
ALTER TABLE [dbo].[t_vip] ADD CONSTRAINT [UQ__t_vip__cardid] UNIQUE NONCLUSTERED ([cardid] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table t_vip
-- ----------------------------
ALTER TABLE [dbo].[t_vip] ADD CONSTRAINT [PK__t_vip__id] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO

